package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.Car;
import com.wanmait.hotelManage.vo.Pager;
import java.sql.Timestamp;

import com.wanmait.hotelManage.util.DBHelper;

public class CarDAO {
	private CarDAO() {};
	private static CarDAO carDAO=new CarDAO();
	public static CarDAO getCarDAO() {
		return carDAO;
	}
	/**
	 * 查询所有车
	 * @return
	 */
	public List<Car> findAll(Pager pager){
		List<Car>cars=new ArrayList<Car>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from car where enable=1 order by car.input_time desc limit ?,?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			Car car=null;
			while(rs.next()) {
				car=new Car();
				car.setId(rs.getInt("id"));
				car.setNumber(rs.getString("number"));
				car.setInputTime(rs.getTimestamp("input_time"));
				car.setEnable(rs.getBoolean("enable"));
				car.setPrice(rs.getDouble("price"));
				car.setCheck(rs.getBoolean("check"));
				cars.add(car);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return cars;
	}
	public int getrows() {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select count(*) c from car  where enable=1";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				int rows=rs.getInt("c");
				return rows;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
	/**
	 * 删除
	 * @param id
	 */
	public void del(Integer id) {
	 String sql="update car set enable=0 where id=?";
	 DBHelper.getDBHelper().executeUpdate(sql, id);
	}
	/**
	 * 根据id查找所有
	 * @param id
	 * @return
	 */
	public Car findthis(Integer id) {
		Car car=null;
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from car where id=? and enable=1";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setInt(1, id);
			rs=ps.executeQuery();
			if(rs.next()) {
				car=new Car();
				car.setId(rs.getInt("id"));
				car.setNumber(rs.getString("number"));
				car.setPrice(rs.getDouble("price"));
				car.setInputTime(rs.getTimestamp("input_time"));
				car.setEnable(rs.getBoolean("enable"));
				car.setCheck(rs.getBoolean("check"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return car;
	}
	/**
	 * 更新
	 * @param flag
	 * @param id
	 * @param price 
	 */
	public void update(Boolean flag, Integer id) {
		String sql="update car set car.check=? where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, flag,id);
	}
//	public static void main(String[] args) {
//		CarDAO.getCarDAO().insert(555, false);
//	}
	/**
	 * 不一定要
	 * @param number
	 * @param price
	 */
//	public void insert(String number, Boolean flag, Double price) {
//		String sql="insert into car(number,car.check,car.price) values(?,?,?)";
//		DBHelper.getDBHelper().executeUpdate(sql, number,flag,price);
//	}
	public void insert(String number, Double price) {
		String sql="insert into car(number,car.price) values(?,?)";
		DBHelper.getDBHelper().executeUpdate(sql, number,price);
	}
	
	public Boolean search(Timestamp ttt, Timestamp tttt) {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql=" SELECT COUNT(*) <( " + 
				"	SELECT COUNT(*) " + 
				"	FROM car  " + 
				"	WHERE ENABLE=1 " + 
				"	) c" + 
				" FROM orders " + 
				" WHERE (? < car_end_time  " + 
				" AND ? >= car_start_time) " + 
				" OR  " + 
				" (? <= car_end_time  " + 
				" AND ? > car_start_time) " + 
				" AND ENABLE=1 " ;
		
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			ps.setTimestamp(1, ttt);
			ps.setTimestamp(2, ttt);
			ps.setTimestamp(3, tttt);
			ps.setTimestamp(4, tttt);
			rs=ps.executeQuery();
			rs.next();
			return rs.getBoolean("c");
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return null;
	}
	public Car getCar() {
		// TODO Auto-generated method stub
		Car car=new Car();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		
		String sql="select * from car where id=1";
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				car.setPrice(rs.getDouble("price"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		
		return car;
	}
	
	public int getrowss(Boolean flag) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select count(*) c from car  where enable=1 and car.check=?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setBoolean(1, flag);
			rs=ps.executeQuery();
			if(rs.next()) {
				int rows=rs.getInt("c");
				return rows;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		int rows=CarDAO.getCarDAO().getrowss(false);
//		System.out.println(rows);
//	}
	public List<Car> findcheck(Boolean flag, Pager pager) {
		List<Car>cars=new ArrayList<Car>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from car where enable=1 and car.check=? order by car.input_time desc limit ?,?";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			ps.setBoolean(1, flag);
			ps.setInt(2, pager.getStart());
			ps.setInt(3, pager.getPageSize());
			rs=ps.executeQuery();
			Car car=null;
			while(rs.next()) {
				car=new Car();
				car.setId(rs.getInt("id"));
				car.setNumber(rs.getString("number"));
				car.setInputTime(rs.getTimestamp("input_time"));
				car.setEnable(rs.getBoolean("enable"));
				car.setPrice(rs.getDouble("price"));
				car.setCheck(rs.getBoolean("check"));
				cars.add(car);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return cars;
	}
	//获得价钱
	public Double findprice() {
		// TODO Auto-generated method stub
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select price from car";
		try {
			conn=DBHelper.getDBHelper().getConn();
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				return rs.getDouble("price");
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return null;
	}
	public Double findPrice() {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="select * from car";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			if(rs.next()) {
				Double price=rs.getDouble("price");
				return price;
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return null;
	}
//	public static void main(String[] args) {
//		Double price=CarDAO.getCarDAO().findPrice();
//		System.out.println(price);
//	}
	/**
	 * 修改全部价钱
	 * @param price
	 */
	public void updatePrice(Double price) {
		String sql="update car set price=?";
		DBHelper.getDBHelper().executeUpdate(sql, price);
	}
}
